package chen.web.forum;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.util.Assert;

import chen.web.DataSourceProvider;
import chen.web.MyContextProvider;
import chen.web.user.Account;
import chen.web.util.DateInt;

class ReplyManager {

	private static SimpleJdbcTemplate simpleJdbcTemplate = new SimpleJdbcTemplate(DataSourceProvider.getDataSource());
	private static JdbcTemplate  jdbcTemplate  = new JdbcTemplate(DataSourceProvider.getDataSource());
	
	private SubjectManager sm = null;
	private ForumAccountLoader fal = null;
	ReplyManager(ForumAccountLoader fal, SubjectManager sm){
		this.fal = fal;
		this.sm = sm;
	}
	
	private static final String SQL_99 = "select * from web_subject_reply where reply_id = ?";
	public Reply getReply(int replyId) {
		// TODO Auto-generated method stub
		Reply reply = null;
		try{
			reply = simpleJdbcTemplate.queryForObject(SQL_99, new ReplyRowMapper(), replyId);
			Subject belong = reply.getBelongSubject();
			reply.setBelongSubject(sm.getSubject(belong.getId()));
		} catch(EmptyResultDataAccessException e){
			//do nothing
		}
		return reply;
	}
	
	private static final String SQL_4 = "delete from web_subject_reply where reply_id = ?";
	public void delete(Reply reply) {
		// TODO Auto-generated method stub
		simpleJdbcTemplate.update(SQL_4, reply.getId());
	}
	
	private static final String SQL_56 = "select * from web_subject_reply where sub_id = ? limit ?, ?";
	public List<Reply> getReplyList(Subject subject, int beginOffset, int count) {
		// TODO Auto-generated method stub
		int id = subject.getId();
		List<Reply> replyList = simpleJdbcTemplate.query(SQL_56, new ReplyRowMapper(), id, beginOffset, count);
		List<Integer> authorIds = new ArrayList<Integer>();
		for(Reply reply : replyList){
			reply.setBelongSubject(subject);
			int authorId = reply.getAuthor().getId();
			authorIds.add(authorId);
		}
		List<ForumAccount> authorList = fal.loadForumAccount(authorIds);
		for(int i=0; i<replyList.size(); i++){
			replyList.get(i).setAuthor(authorList.get(i));
		}
		return replyList;
	}
	
	
	private static final String SQL_32 = "update web_subject_reply set reply_content = ?, reply_flag = ? , reply_invisible = ? where reply_id = ?";
	public void update(Reply reply){
		Assert.hasLength(reply.getContent());
		Assert.isTrue(reply.getId() > 0);
		int invisiable = reply.isFakeDelete()?1:reply.isNeedChecking()?2:0;
		simpleJdbcTemplate.update(SQL_32, reply.getContent(), reply.getFlag(), invisiable, reply.getId());
	}
	
	private static final String SQL_22="insert into web_subject_reply(sub_id, reply_content, reply_authorId, reply_authorNickname, reply_dateline, reply_ip, reply_flag) values(?,?,?,?,?,?,?);";
	public Reply post(final Reply reply) {
		// TODO Auto-generated method stub
		Assert.hasLength(reply.getContent());
		final Subject belongSubject = reply.getBelongSubject();
		Assert.notNull(belongSubject);
		reply.setIp(MyContextProvider.getMyContext().getIp());
		reply.setAuthor(fal.loadForumAccount(MyContextProvider.getMyContext().getAccount()));
		reply.setDate(new Date());
		reply.setNeedChecking(reply.getBelongSubject().getCategory().isCheckReply());
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(new PreparedStatementCreator(){
			@Override
			public PreparedStatement createPreparedStatement(Connection conn)
					throws SQLException {
				// TODO Auto-generated method stub
				PreparedStatement ps = conn.prepareStatement(SQL_22, PreparedStatement.RETURN_GENERATED_KEYS);
				ps.setInt(1, belongSubject.getId());
				ps.setString(2, reply.getContent());
				ps.setInt(3, reply.getAuthor().getId());
				ps.setString(4, reply.getAuthor().getAccount().getNickname());	
				ps.setInt(5, DateInt.transform(reply.getDate())); 
				ps.setString(6, reply.getIp());
				ps.setInt(7, reply.getFlag());	
				return ps;
			}
		
		}, keyHolder);
		int id = keyHolder.getKey().intValue();
		reply.setId(id);
		return reply;
	}
	
	private static final String SQL_45 = "select count(*) from web_subject_reply where reply_authorId = ?";
	public int replyAmount(Account acc) {
		// TODO Auto-generated method stub
		int id = acc.getId();
		int count = simpleJdbcTemplate.queryForInt(SQL_45, id);
		return count;
	}
	
	public List<Reply> getReplyList(ReplyCondition condition, int beginOffset, int count) {
		// TODO Auto-generated method stub
		Assert.notNull(condition);
		StringBuffer sql = new StringBuffer();
		//多表
		if(condition.getCategory()!=null){
			sql.append("select web_subject_reply.* from web_subject，web_subject_reply");
		}else{
			sql.append("select * from web_subject_reply");
		}
		sql.append(condition.toSql())
			.append("limit ?,?");
		List<Reply> replyList = simpleJdbcTemplate.query(sql.toString(), new ReplyRowMapper(), beginOffset, count);
		//*************************有性能问题需要解决*****************************************
		for(Reply reply : replyList){
			if(condition.getAccount() != null){
				reply.setAuthor(condition.getAccount());
			} else {
				int authorId = reply.getAuthor().getId();
				ForumAccount author = fal.loadForumAccount(authorId);
				reply.setAuthor(author);	
			}
			if(condition.getSubject() != null){
				reply.setBelongSubject(condition.getSubject());	
			} else {
				Subject subject = sm.getSubject(reply.getBelongSubject().getId());
				reply.setBelongSubject(subject);
			}
		}
		return replyList;
	}
	
	private static final String SQL_78 = "select count(*) from web_subject_reply where sub_id = ? and reply_id <= ?";
	public int replyOrderInTopic(Reply reply) {
		// TODO Auto-generated method stub
		int subjectId = reply.getBelongSubject().getId();
		int replyId = reply.getId();
		int order = simpleJdbcTemplate.queryForInt(SQL_78, subjectId, replyId);
		return order;
	}
	
	public int totalReply(ReplyCondition condition) {
		Assert.notNull(condition);
		condition.setOrder(null);
		StringBuffer sql = new StringBuffer();
		//多表
		if(condition.getCategory()!=null){
			sql.append("select count(*) from web_subject,web_subject_reply");
		}else{
			sql.append("select count(*) from web_subject_reply");
		}
		sql.append(condition.toSql());
		int total = simpleJdbcTemplate.queryForInt(sql.toString());
		return total;
	}
	
	public static final class ReplyRowMapper implements RowMapper<Reply>{
		@Override
		public Reply mapRow(ResultSet rs, int rowNum) throws SQLException {
			// TODO Auto-generated method stub
			Reply reply = new Reply();
			reply.setContent(rs.getString("reply_content"));
			reply.setDate(DateInt.transform(rs.getInt("reply_dateline")));
			reply.setFlag(rs.getInt("reply_flag"));
			reply.setIp(rs.getString("reply_ip"));
			reply.setId(rs.getInt("reply_id"));
			int invisible = rs.getInt("reply_invisible");
			if(invisible==1){
				reply.setFakeDelete(true);
			}
			if(invisible==2){
				reply.setNeedChecking(true);
			}
			
			int authorId = rs.getInt("reply_authorId");
			Account author = new Account(authorId);
			reply.setAuthor(new ForumAccount(author));
			Subject belong = new Subject();
			belong.setId(rs.getInt("sub_id"));
			reply.setBelongSubject(belong);
			return reply;
		}
	}
	
/*	private static final String SQL_76 = "select count(*) from web_subject, web_subject_reply where web_subject.sub_id = web_subject_reply.sub_id and reply_invisible = 0 and cat_id in(#cat_id#)";
	public int totalReply(Category category, boolean containsChildren) {
		String replacement = SubjectCondition.getCategoryIdStr(category, containsChildren);
		String sql = SQL_76.replaceAll("#cat_id#", replacement);
		int count = simpleJdbcTemplate.queryForInt(sql);
		return count;
	}*/
}
